﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DelonixRegiaSystem.Classes
{
    public class roomAvailabilityDBManager
    {
        public static int InsertRoom(room r)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO room(Status,roomType,roomFloor,roomRates)"
                    + " VALUES (@Status,@roomType,@roomFloor,@roomRates)";
                comm.Parameters.AddWithValue("@Status", r.Status);
                comm.Parameters.AddWithValue("@roomType", r.roomType);
                comm.Parameters.AddWithValue("@roomFloor", r.roomFloor);
                comm.Parameters.AddWithValue("@roomRates", r.roomRates);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }
        public static int UpdateRoom(room c)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE room SET Status=@Status, roomType=@roomType, roomFloor=@roomFloor, roomRates=@roomRates where roomNum=@roomNum";
                comm.Parameters.AddWithValue("@Status", c.Status);
                comm.Parameters.AddWithValue("@roomType", c.roomType);
                comm.Parameters.AddWithValue("@roomFloor", c.roomFloor);
                comm.Parameters.AddWithValue("@roomRates", c.roomRates);
                comm.Parameters.AddWithValue("@roomNum", c.roomNum);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }
        public static room GetRoomByRoomNum(int roomNum)
        {
            room r = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from room WHERE roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    r = new room();
                    r.roomNum = (int)dr["roomNum"];
                    r.Status = (string)dr["Status"];
                    r.roomType = (string)dr["roomType"];
                    r.roomFloor = (string)dr["roomFloor"];
                    r.roomRates = (int)dr["roomRates"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return r;
        }
        
        public static ArrayList GetAllRooms()
        {
            ArrayList Room = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from room";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    room r = new room();
                    r.roomNum = (int)dr["roomNum"];
                    r.Status = (string)dr["Status"];
                    r.roomType = (string)dr["roomType"];
                    r.roomFloor = (string)dr["roomFloor"];
                    Room.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return Room;
        }
        public static int DeleteRoom(int roomNum)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from room where roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
        public static int UpdateRoomStatus(room c)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE room SET Status=@Status where roomNum=@roomNum";
                comm.Parameters.AddWithValue("@Status", c.Status);
                comm.Parameters.AddWithValue("@roomNum", c.roomNum);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }
    }
}
